package common;


import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

public class DButil {
	private static DButil instance = new DButil();
	String url = "jdbc:mysql://127.0.0.1:3306/library?useUnicode=true&characterEncoding=UTF-8";
	String user = "root";
	String password = "123456";
	String driver="com.mysql.jdbc.Driver";
	
	private DButil(){}
	
	public static DButil getInstance(){
		return instance;
	}
	private ThreadLocal<Connection> threads = new ThreadLocal<Connection>();
		
	private Connection getConnection(){
		Connection conn = threads.get();
		if( conn == null ){
			try {
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, password);
				threads.set(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return conn;
	}
	
	public int getCount(String sql){
		return Integer.parseInt(excuteQuery(sql).get(0)[0]);
	}
	
	public int  excuteUpdate(String sql){
		Connection conn = getConnection();		
		Statement st = null;
		try {
			st = conn.createStatement();
			return st.executeUpdate(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if( st != null ){
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	public List<String[]> excuteQuery(String sql){
		List<String[]> list = new ArrayList<String[]>();
		Connection conn = getConnection();		
		Statement st = null;
		ResultSet result = null;
		try {
			st = conn.createStatement();
			result = st.executeQuery(sql);
			int column = result.getMetaData().getColumnCount();
			while(result.next()){
				String[] row = new String[column];
				list.add(row);
				for(int i=0; i<column; i++){
					row[i] = result.getString(i+1);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if( result != null ){
			try {
				result.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if( st != null ){
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public List<Object> excuteQuery(String sql, Class c){
		List<Object> list = new ArrayList<Object>();
		Connection conn = getConnection();		
		Statement st = null;
		ResultSet result = null;
		try {
			st = conn.createStatement();
			result = st.executeQuery(sql);
			ResultSetMetaData metaData = result.getMetaData();
			int column = metaData.getColumnCount();
			int[] columnTypes = new int[column];
			String[] methodNames = new String[column];
			for(int i=1; i<=column; i++){
				String columnName = metaData.getColumnName(i);				
				columnTypes[i-1] = metaData.getColumnType(i);
				;
				methodNames[i-1] = "set"+String.valueOf(columnName.charAt(0)).toUpperCase()+columnName.substring(1, columnName.length());
			}
			
			while(result.next()){
				Object obj = c.newInstance();
				list.add(obj);				
				for(int i=1; i<=column; i++){
					if( columnTypes[i-1] == Types.VARCHAR){
						String value = result.getString(i);
						Method m = c.getMethod(methodNames[i-1], String.class);
						if( m != null ){
							m.invoke(obj, value);
						}
					}
					else if( columnTypes[i-1] == Types.INTEGER || columnTypes[i-1] == Types.BIGINT || columnTypes[i-1] == Types.TINYINT ){
						int value = result.getInt(i);
						Method m = c.getMethod(methodNames[i-1], int.class);
						if( m != null ){
							m.invoke(obj, value);
						}
					}
					else if( columnTypes[i-1] == Types.FLOAT || columnTypes[i-1] == Types.DECIMAL || columnTypes[i-1] == Types.REAL){
						double value = result.getDouble(i);
						Method m = c.getMethod(methodNames[i-1], double.class);
						if( m != null ){
							m.invoke(obj, value);
						}
					}
					else if( columnTypes[i-1] == Types.DATE ){
						java.sql.Date value = result.getDate(i);	
						if(value !=null){
							java.util.Date d=new java.util.Date (value.getTime());
							Method m = c.getMethod(methodNames[i-1], java.util.Date.class);
							if( m != null ){
								m.invoke(obj, value);
							}
						}
						
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		if( result != null ){
			try {
				result.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if( st != null ){
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public void close(){
		Connection conn = threads.get();
		if( conn != null ){
			threads.remove();
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
